﻿// --------------------------------------------------------------------------------------------------------------------
// <copyright file="GenericRepository.cs" company="">
//   
// </copyright>
// <summary>
//   Generic repository
// </summary>
// --------------------------------------------------------------------------------------------------------------------

#region

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Entity;
using System.Data.Entity.Infrastructure;
using System.Linq;
using System.Linq.Expressions;
using Common.Data.Specifications;
using Common.Data.UnitOfWork;
using System.Data.OracleClient;
using Common.Data.Repository.Enum;
using System.Web;
using System.Reflection;
using System.Configuration;
using System.Data.Objects;
using System.Data.Metadata.Edm;

#endregion

namespace Common.Data.Repository
{
    /// <summary>
    ///     Generic repository
    /// </summary>
    public class GenericRepository : IRepository
    {
        /// <summary>
        /// The connection string
        /// </summary>
        private static string _connectionString = string.Empty;

        /// <summary>
        /// The _connection string name.
        /// </summary>
        private readonly string _connectionStringName;

        /// <summary>
        /// The _context.
        /// </summary>
        private DbContext _context;

        /// <summary>
        /// The unit of work.
        /// </summary>
        private IUnitOfWork unitOfWork;

        /// <summary>
        /// Initializes a new instance of the <see cref="GenericRepository"/> class. 
        ///     Initializes a new instance of the <see cref="Repository&lt;TEntity&gt;"/> class.
        /// </summary>
        public GenericRepository()
            : this(string.Empty)
        {
        }

        /// <summary>
        /// Initializes a new instance of the <see cref="GenericRepository"/> class. 
        /// Initializes a new instance of the <see cref="GenericRepository&lt;TEntity&gt;"/> class.
        /// </summary>
        /// <param name="connectionStringName">
        /// Name of the connection string.
        /// </param>
        public GenericRepository(string connectionStringName)
        {
            _connectionStringName = connectionStringName;
        }

        /// <summary>
        /// Initializes a new instance of the <see cref="GenericRepository"/> class. 
        /// Initializes a new instance of the <see cref="GenericRepository&lt;TEntity&gt;"/> class.
        /// </summary>
        /// <param name="context">
        /// The context.
        /// </param>
        public GenericRepository(DbContext context)
        {
            if (context == null)
                throw new ArgumentNullException("context");
            _context = context;
            if (HttpContext.Current != null)
            {
                _connectionString = (HttpContext.Current.Items["ConnectionString"] == null ? _context.Database.Connection.ConnectionString : (string)HttpContext.Current.Items["ConnectionString"]);
                if (HttpContext.Current.Items["ConnectionString"] == null) HttpContext.Current.Items["ConnectionString"] = _connectionString;
            }
            else
            {
                if (string.IsNullOrEmpty(_connectionString))
                {
                    try
                    {
                        _connectionString = _context.Database.Connection.ConnectionString;
                    }
                    catch
                    { }
                }

                if (string.IsNullOrEmpty(_connectionString))
                {
                    try
                    {
                        var connectionString = ConfigurationManager.AppSettings["ConnectionString"];
                        _connectionString = ConfigurationManager.ConnectionStrings[connectionString].ConnectionString;
                    }
                    catch
                    { }
                }
            }
        }


        /// <summary>
        /// Initializes a new instance of the <see cref="GenericRepository"/> class.
        /// </summary>
        /// <param name="context">
        /// The context.
        /// </param>
        /// <exception cref="ArgumentNullException">
        /// </exception>
        public GenericRepository(ObjectContext context)
        {
            if (context == null)
                throw new ArgumentNullException("context");
            _context = new DbContext(context, true);
            if (HttpContext.Current != null)
            {
                _connectionString = (HttpContext.Current.Items["ConnectionString"] == null ? _context.Database.Connection.ConnectionString : (string)HttpContext.Current.Items["LgevhConnectionString"]);
                if (HttpContext.Current.Items["ConnectionString"] == null) HttpContext.Current.Items["ConnectionString"] = _connectionString;
            }
            else
            {
                if (string.IsNullOrEmpty(_connectionString)) _connectionString = _context.Database.Connection.ConnectionString;
            }
        }

        /// <summary>
        /// Gets the db context.
        /// </summary>
        public DbContext DbContext
        {
            get
            {
                if (_context == null)
                {
                    if (string.IsNullOrEmpty(_connectionStringName))
                        _context = DbContextManager.Current;
                    else
                        _context = DbContextManager.CurrentFor(_connectionStringName);
                }

                return _context;
            }
        }

        /// <summary>
        /// The get by key.
        /// </summary>
        /// <param name="keyValue">
        /// The key value.
        /// </param>
        /// <typeparam name="TEntity">
        /// </typeparam>
        /// <returns>
        /// The <see cref="TEntity"/>.
        /// </returns>
        public TEntity GetByKey<TEntity>(object keyValue) where TEntity : class
        {
            EntityKey key = GetEntityKey<TEntity>(keyValue);

            object originalItem;
            if (((IObjectContextAdapter)DbContext).ObjectContext.TryGetObjectByKey(key, out originalItem))
            {
                return (TEntity)originalItem;
            }

            return default(TEntity);
        }

        /// <summary>
        /// The get query.
        /// </summary>
        /// <typeparam name="TEntity">
        /// </typeparam>
        /// <returns>
        /// The <see cref="IQueryable"/>.
        /// </returns>
        public IQueryable<TEntity> GetQuery<TEntity>() where TEntity : class
        {
            /* 
             * From CTP4, I could always safely call this to return an IQueryable on DbContext 
             * then performed any with it without any problem:
             */
            // return DbContext.Set<TEntity>();

            /*
             * but with 4.1 release, when I call GetQuery<TEntity>().AsEnumerable(), there is an exception:
             * ... System.ObjectDisposedException : The ObjectContext instance has been disposed and can no longer be used for operations that require a connection.
             */

            // here is a work around: 
            // - cast DbContext to IObjectContextAdapter then get ObjectContext from it
            // - call CreateQuery<TEntity>(entityName) method on the ObjectContext
            // - perform querying on the returning IQueryable, and it works!
            var entityName = GetEntityName<TEntity>();
            return ((IObjectContextAdapter)DbContext).ObjectContext.CreateQuery<TEntity>(entityName);
        }

        /// <summary>
        /// The get query.
        /// </summary>
        /// <param name="predicate">
        /// The predicate.
        /// </param>
        /// <typeparam name="TEntity">
        /// </typeparam>
        /// <returns>
        /// The <see cref="IQueryable"/>.
        /// </returns>
        public IQueryable<TEntity> GetQuery<TEntity>(Expression<Func<TEntity, bool>> predicate) where TEntity : class
        {
            return GetQuery<TEntity>().Where(predicate);
        }

        /// <summary>
        /// The get query.
        /// </summary>
        /// <param name="criteria">
        /// The criteria.
        /// </param>
        /// <typeparam name="TEntity">
        /// </typeparam>
        /// <returns>
        /// The <see cref="IQueryable"/>.
        /// </returns>
        public IQueryable<TEntity> GetQuery<TEntity>(ISpecification<TEntity> criteria) where TEntity : class
        {
            return criteria.SatisfyingEntitiesFrom(GetQuery<TEntity>());
        }

        /// <summary>
        /// The get.
        /// </summary>
        /// <param name="orderBy">
        /// The order by.
        /// </param>
        /// <param name="pageIndex">
        /// The page index.
        /// </param>
        /// <param name="pageSize">
        /// The page size.
        /// </param>
        /// <param name="sortOrder">
        /// The sort order.
        /// </param>
        /// <typeparam name="TEntity">
        /// </typeparam>
        /// <typeparam name="TOrderBy">
        /// </typeparam>
        /// <returns>
        /// The <see cref="IEnumerable"/>.
        /// </returns>
        public IEnumerable<TEntity> Get<TEntity, TOrderBy>(Expression<Func<TEntity, TOrderBy>> orderBy, int pageIndex,
            int pageSize, SortOrder sortOrder = SortOrder.Ascending) where TEntity : class
        {
            if (sortOrder == SortOrder.Ascending)
            {
                return GetQuery<TEntity>().OrderBy(orderBy).Skip((pageIndex - 1) * pageSize).Take(pageSize).AsEnumerable();
            }

            return
                GetQuery<TEntity>()
                    .OrderByDescending(orderBy)
                    .Skip((pageIndex - 1) * pageSize)
                    .Take(pageSize)
                    .AsEnumerable();
        }

        /// <summary>
        /// The get.
        /// </summary>
        /// <param name="criteria">
        /// The criteria.
        /// </param>
        /// <param name="orderBy">
        /// The order by.
        /// </param>
        /// <param name="pageIndex">
        /// The page index.
        /// </param>
        /// <param name="pageSize">
        /// The page size.
        /// </param>
        /// <param name="sortOrder">
        /// The sort order.
        /// </param>
        /// <typeparam name="TEntity">
        /// </typeparam>
        /// <typeparam name="TOrderBy">
        /// </typeparam>
        /// <returns>
        /// The <see cref="IEnumerable"/>.
        /// </returns>
        public IEnumerable<TEntity> Get<TEntity, TOrderBy>(Expression<Func<TEntity, bool>> criteria,
            Expression<Func<TEntity, TOrderBy>> orderBy, int pageIndex, int pageSize,
            SortOrder sortOrder = SortOrder.Ascending) where TEntity : class
        {
            if (sortOrder == SortOrder.Ascending)
            {
                return GetQuery(criteria).OrderBy(orderBy).Skip((pageIndex - 1) * pageSize).Take(pageSize).AsEnumerable();
            }

            return
                GetQuery(criteria)
                    .OrderByDescending(orderBy)
                    .Skip((pageIndex - 1) * pageSize)
                    .Take(pageSize)
                    .AsEnumerable();
        }

        /// <summary>
        /// The get.
        /// </summary>
        /// <param name="specification">
        /// The specification.
        /// </param>
        /// <param name="orderBy">
        /// The order by.
        /// </param>
        /// <param name="pageIndex">
        /// The page index.
        /// </param>
        /// <param name="pageSize">
        /// The page size.
        /// </param>
        /// <param name="sortOrder">
        /// The sort order.
        /// </param>
        /// <typeparam name="TEntity">
        /// </typeparam>
        /// <typeparam name="TOrderBy">
        /// </typeparam>
        /// <returns>
        /// The <see cref="IEnumerable"/>.
        /// </returns>
        public IEnumerable<TEntity> Get<TEntity, TOrderBy>(ISpecification<TEntity> specification,
            Expression<Func<TEntity, TOrderBy>> orderBy, int pageIndex, int pageSize,
            SortOrder sortOrder = SortOrder.Ascending) where TEntity : class
        {
            if (sortOrder == SortOrder.Ascending)
            {
                return
                    specification.SatisfyingEntitiesFrom(GetQuery<TEntity>())
                        .OrderBy(orderBy)
                        .Skip((pageIndex - 1) * pageSize)
                        .Take(pageSize)
                        .AsEnumerable();
            }

            return
                specification.SatisfyingEntitiesFrom(GetQuery<TEntity>())
                    .OrderByDescending(orderBy)
                    .Skip((pageIndex - 1) * pageSize)
                    .Take(pageSize)
                    .AsEnumerable();
        }

        /// <summary>
        /// The single.
        /// </summary>
        /// <param name="criteria">
        /// The criteria.
        /// </param>
        /// <typeparam name="TEntity">
        /// </typeparam>
        /// <returns>
        /// The <see cref="TEntity"/>.
        /// </returns>
        public TEntity Single<TEntity>(Expression<Func<TEntity, bool>> criteria) where TEntity : class
        {
            return GetQuery<TEntity>().Single<TEntity>(criteria);
        }

        /// <summary>
        /// The single.
        /// </summary>
        /// <param name="criteria">
        /// The criteria.
        /// </param>
        /// <typeparam name="TEntity">
        /// </typeparam>
        /// <returns>
        /// The <see cref="TEntity"/>.
        /// </returns>
        public TEntity Single<TEntity>(ISpecification<TEntity> criteria) where TEntity : class
        {
            return criteria.SatisfyingEntityFrom(GetQuery<TEntity>());
        }

        /// <summary>
        /// The first.
        /// </summary>
        /// <param name="predicate">
        /// The predicate.
        /// </param>
        /// <typeparam name="TEntity">
        /// </typeparam>
        /// <returns>
        /// The <see cref="TEntity"/>.
        /// </returns>
        public TEntity First<TEntity>(Expression<Func<TEntity, bool>> predicate) where TEntity : class
        {
            return GetQuery<TEntity>().First(predicate);
        }

        /// <summary>
        /// The first.
        /// </summary>
        /// <param name="criteria">
        /// The criteria.
        /// </param>
        /// <typeparam name="TEntity">
        /// </typeparam>
        /// <returns>
        /// The <see cref="TEntity"/>.
        /// </returns>
        public TEntity First<TEntity>(ISpecification<TEntity> criteria) where TEntity : class
        {
            return criteria.SatisfyingEntitiesFrom(GetQuery<TEntity>()).First();
        }

        /// <summary>
        /// The add.
        /// </summary>
        /// <param name="entity">
        /// The entity.
        /// </param>
        /// <typeparam name="TEntity">
        /// </typeparam>
        /// <exception cref="ArgumentNullException">
        /// </exception>
        public void Add<TEntity>(TEntity entity) where TEntity : class
        {
            if (entity == null)
            {
                throw new ArgumentNullException("entity");
            }

            DbContext.Set<TEntity>().Add(entity);
        }

        /// <summary>
        /// The add a list of entiti to Db.
        /// </summary>
        /// <typeparam name="TEntity"></typeparam>
        /// <param name="entities"></param>
        public void AddRange<TEntity>(IEnumerable<TEntity> entities, bool recreateContext = true) where TEntity : class
        {
            int commitCount = 100;
            DbContext.Configuration.AutoDetectChangesEnabled = false;
            DbContext.Configuration.ValidateOnSaveEnabled = false;
            int count = entities == null ? 0 : entities.Count();
            for (int i = 0; i < count; i++)
            {
                var entity = entities.ElementAt(i);
                Add(entity);
                if (i == 0 || i % commitCount != 0) continue;

                DbContext.SaveChanges();
            }
            DbContext.SaveChanges();
            DbContext.Configuration.AutoDetectChangesEnabled = true;
            DbContext.Configuration.ValidateOnSaveEnabled = true;

            if (recreateContext)
            {
                DbContext.Dispose();
                _context = null;
                _context = this.DbContext;
            }
        }


        /// <summary>
        /// The attach.
        /// </summary>
        /// <param name="entity">
        /// The entity.
        /// </param>
        /// <typeparam name="TEntity">
        /// </typeparam>
        /// <exception cref="ArgumentNullException">
        /// </exception>
        public void Attach<TEntity>(TEntity entity) where TEntity : class
        {
            if (entity == null)
            {
                throw new ArgumentNullException("entity");
            }

            DbContext.Set<TEntity>().Attach(entity);
        }

        /// <summary>
        /// The delete.
        /// </summary>
        /// <param name="entity">
        /// The entity.
        /// </param>
        /// <typeparam name="TEntity">
        /// </typeparam>
        /// <exception cref="ArgumentNullException">
        /// </exception>
        public void Delete<TEntity>(TEntity entity) where TEntity : class
        {
            if (entity == null)
            {
                throw new ArgumentNullException("entity");
            }

            DbContext.Set<TEntity>().Remove(entity);
        }

        /// <summary>
        /// The delete.
        /// </summary>
        /// <param name="criteria">
        /// The criteria.
        /// </param>
        /// <typeparam name="TEntity">
        /// </typeparam>
        public void Delete<TEntity>(Expression<Func<TEntity, bool>> criteria) where TEntity : class
        {
            IEnumerable<TEntity> records = Find(criteria);

            foreach (TEntity record in records)
            {
                Delete(record);
            }
        }

        /// <summary>
        /// The delete.
        /// </summary>
        /// <param name="criteria">
        /// The criteria.
        /// </param>
        /// <typeparam name="TEntity">
        /// </typeparam>
        public void Delete<TEntity>(ISpecification<TEntity> criteria) where TEntity : class
        {
            IEnumerable<TEntity> records = Find(criteria);
            foreach (TEntity record in records)
            {
                Delete(record);
            }
        }

        /// <summary>
        /// The get all.
        /// </summary>
        /// <typeparam name="TEntity">
        /// </typeparam>
        /// <returns>
        /// The <see cref="IEnumerable"/>.
        /// </returns>
        public IEnumerable<TEntity> GetAll<TEntity>() where TEntity : class
        {
            return GetQuery<TEntity>().AsEnumerable();
        }

        /// <summary>
        /// The update.
        /// </summary>
        /// <param name="entity">
        /// The entity.
        /// </param>
        /// <typeparam name="TEntity">
        /// </typeparam>
        public void Update<TEntity>(TEntity entity) where TEntity : class
        {
            var fqen = GetEntityName<TEntity>();

            object originalItem;
            EntityKey key = ((IObjectContextAdapter)DbContext).ObjectContext.CreateEntityKey(fqen, entity);
            if (((IObjectContextAdapter)DbContext).ObjectContext.TryGetObjectByKey(key, out originalItem))
            {
                ((IObjectContextAdapter)DbContext).ObjectContext.ApplyCurrentValues(key.EntitySetName, entity);
            }
        }

        /// <summary>
        /// re-update dbcontext
        /// </summary>
        /// <param name="dbContext"></param>
        public void UpdateContext(DbContext dbContext)
        {
            _context = dbContext;
            unitOfWork = new UnitOfWork.UnitOfWork(_context);
        }

        /// <summary>
        /// The find.
        /// </summary>
        /// <param name="criteria">
        /// The criteria.
        /// </param>
        /// <typeparam name="TEntity">
        /// </typeparam>
        /// <returns>
        /// The <see cref="IEnumerable"/>.
        /// </returns>
        public IEnumerable<TEntity> Find<TEntity>(Expression<Func<TEntity, bool>> criteria) where TEntity : class
        {
            return GetQuery<TEntity>().Where(criteria);
        }

        /// <summary>
        /// The find one.
        /// </summary>
        /// <param name="criteria">
        /// The criteria.
        /// </param>
        /// <typeparam name="TEntity">
        /// </typeparam>
        /// <returns>
        /// The <see cref="TEntity"/>.
        /// </returns>
        public TEntity FindOne<TEntity>(Expression<Func<TEntity, bool>> criteria) where TEntity : class
        {
            return GetQuery<TEntity>().Where(criteria).FirstOrDefault();
        }

        /// <summary>
        /// The find one.
        /// </summary>
        /// <param name="criteria">
        /// The criteria.
        /// </param>
        /// <typeparam name="TEntity">
        /// </typeparam>
        /// <returns>
        /// The <see cref="TEntity"/>.
        /// </returns>
        public TEntity FindOne<TEntity>(ISpecification<TEntity> criteria) where TEntity : class
        {
            return GetQuery<TEntity>(criteria).FirstOrDefault();
        }

        /// <summary>
        /// The find.
        /// </summary>
        /// <param name="criteria">
        /// The criteria.
        /// </param>
        /// <typeparam name="TEntity">
        /// </typeparam>
        /// <returns>
        /// The <see cref="IEnumerable"/>.
        /// </returns>
        public IEnumerable<TEntity> Find<TEntity>(ISpecification<TEntity> criteria) where TEntity : class
        {
            return criteria.SatisfyingEntitiesFrom(GetQuery<TEntity>()).AsEnumerable();
        }

        /// <summary>
        /// The count.
        /// </summary>
        /// <typeparam name="TEntity">
        /// </typeparam>
        /// <returns>
        /// The <see cref="int"/>.
        /// </returns>
        public int Count<TEntity>() where TEntity : class
        {
            return GetQuery<TEntity>().Count();
        }

        /// <summary>
        /// The count.
        /// </summary>
        /// <param name="criteria">
        /// The criteria.
        /// </param>
        /// <typeparam name="TEntity">
        /// </typeparam>
        /// <returns>
        /// The <see cref="int"/>.
        /// </returns>
        public int Count<TEntity>(Expression<Func<TEntity, bool>> criteria) where TEntity : class
        {
            return GetQuery<TEntity>().Count(criteria);
        }

        /// <summary>
        /// The count.
        /// </summary>
        /// <param name="criteria">
        /// The criteria.
        /// </param>
        /// <typeparam name="TEntity">
        /// </typeparam>
        /// <returns>
        /// The <see cref="int"/>.
        /// </returns>
        public int Count<TEntity>(ISpecification<TEntity> criteria) where TEntity : class
        {
            return criteria.SatisfyingEntitiesFrom(GetQuery<TEntity>()).Count();
        }

        /// <summary>
        /// Gets the unit of work.
        /// </summary>
        public IUnitOfWork UnitOfWork
        {
            get
            {
                if (unitOfWork == null)
                {
                    unitOfWork = new UnitOfWork.UnitOfWork(DbContext);
                }

                return unitOfWork;
            }
        }

        /// <summary>
        /// The save.
        /// </summary>
        /// <param name="entity">
        /// The entity.
        /// </param>
        /// <typeparam name="TEntity">
        /// </typeparam>
        /// <returns>
        /// The <see cref="TEntity"/>.
        /// </returns>
        public TEntity Save<TEntity>(TEntity entity) where TEntity : class
        {
            Add(entity);
            DbContext.SaveChanges();
            return entity;
        }

        /// <summary>
        /// The get entity key.
        /// </summary>
        /// <param name="keyValue">
        /// The key value.
        /// </param>
        /// <typeparam name="TEntity">
        /// </typeparam>
        /// <returns>
        /// The <see cref="EntityKey"/>.
        /// </returns>
        private EntityKey GetEntityKey<TEntity>(object keyValue) where TEntity : class
        {
            var entitySetName = GetEntityName<TEntity>();
            var objectSet = ((IObjectContextAdapter)DbContext).ObjectContext.CreateObjectSet<TEntity>();
            var keyPropertyName = objectSet.EntitySet.ElementType.KeyMembers[0].ToString();
            var entityKey = new EntityKey(entitySetName, new[] { new EntityKeyMember(keyPropertyName, keyValue) });
            return entityKey;
        }

        /// <summary>
        /// The get entity name.
        /// </summary>
        /// <typeparam name="TEntity">
        /// </typeparam>
        /// <returns>
        /// The <see cref="string"/>.
        /// </returns>
        private string GetEntityName<TEntity>() where TEntity : class
        {
            // PluralizationService pluralizer = PluralizationService.CreateService(CultureInfo.GetCultureInfo("en"));
            // return string.Format("{0}.{1}", ((IObjectContextAdapter)DbContext).ObjectContext.DefaultContainerName, pluralizer.Pluralize(typeof(TEntity).Name));

            // Thanks to Kamyar Paykhan -  http://huyrua.wordpress.com/2011/04/13/entity-framework-4-poco-repository-and-specification-pattern-upgraded-to-ef-4-1/#comment-688
            string entitySetName = ((IObjectContextAdapter)DbContext).ObjectContext
                .MetadataWorkspace
                .GetEntityContainer(((IObjectContextAdapter)DbContext).ObjectContext.DefaultContainerName,
                    DataSpace.CSpace)
                .BaseEntitySets.Where(bes => bes.ElementType.Name == typeof(TEntity).Name).First().Name;
            return string.Format("{0}.{1}", ((IObjectContextAdapter)DbContext).ObjectContext.DefaultContainerName,
                entitySetName);
        }

        /// <summary>
        /// 
        /// </summary>
        /// <typeparam name="TEntity"></typeparam>
        /// <param name="StoreName"></param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        public IEnumerable<TEntity> ExecuteProcedure<TEntity>(string StoreName, params DataParameter[] parameters) //where TEntity : class
        {
            System.Type myType = typeof(TEntity);
            var prop = myType.GetProperties();

            IEnumerable<TEntity> result = null;
            using (var conn = new OracleConnection())
            {
                conn.ConnectionString = _connectionString;
                try
                {
                    using (var command = conn.CreateCommand())
                    {
                        command.CommandType = CommandType.StoredProcedure;
                        command.CommandText = StoreName;
                        if (parameters != null)
                            foreach (DataParameter param in parameters)
                            {
                                OracleParameter oparam = new OracleParameter();
                                if (!string.IsNullOrEmpty(param.Name)) oparam.ParameterName = param.Name;
                                if (param.Type != default(DataType)) oparam.OracleType = DataMaper.Map(param.Type);
                                oparam.Direction = DataMaper.Map(param.Direction);
                                oparam.Value = (param.Value == null ? System.DBNull.Value : param.Value);
                                command.Parameters.Add(oparam);
                            }

                        OracleDataAdapter da = new OracleDataAdapter(command);
                        DataTable tblresult = new DataTable();
                        da.Fill(tblresult);

                        if (tblresult == null || tblresult.Rows.Count == 0)
                        {
                            tblresult.Dispose();
                            tblresult = null;
                            return null;
                        }

                        var dict_columns = new Dictionary<string, string>();
                        var infos = new List<PropertyInfo>();
                        foreach (DataColumn clm in tblresult.Columns) dict_columns.Add(clm.ColumnName.ToUpper(), clm.ColumnName);
                        foreach (var inf in prop)
                        {
                            if (!dict_columns.ContainsKey(inf.Name.ToUpper())) continue;
                            infos.Add(inf);
                        }

                        List<TEntity> lst = new List<TEntity>();
                        foreach (DataRow reader in tblresult.Rows)
                        {
                            if (typeof(TEntity).IsClass)
                            {
                                var entity = (TEntity)Activator.CreateInstance(myType);
                                foreach (var inf in infos)
                                {
                                    try
                                    {
                                        object value = reader[inf.Name];
                                        if (value != System.DBNull.Value) inf.SetValue(entity, value, null);
                                    }
                                    catch
                                    {
                                    }
                                }
                                lst.Add(entity);
                            }
                            else
                            {
                                TEntity item = (TEntity)reader[0];
                                lst.Add(item);
                            }
                        }
                        tblresult.Dispose();
                        tblresult = null;
                        da.Dispose();
                        da = null;
                        dict_columns.Clear();
                        dict_columns = null;
                        result = lst;
                    }
                }
                catch (Exception ex)
                {
                    throw ex;
                }
            }
            return result;
        }


        /// <summary>
        /// 
        /// </summary>
        /// <param name="StoreName"></param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        public int ExecuteProcedure(string StoreName, params DataParameter[] parameters)
        {
            int result = -1;
            using (var conn = new OracleConnection())
            {
                conn.ConnectionString = _connectionString;
                conn.Open();
                using (var command = conn.CreateCommand())
                {
                    command.CommandType = CommandType.StoredProcedure;
                    command.CommandText = StoreName;
                    if (parameters != null)
                        foreach (DataParameter param in parameters)
                        {
                            OracleParameter oparam = new OracleParameter();
                            if (!string.IsNullOrEmpty(param.Name)) oparam.ParameterName = param.Name;
                            if (param.Type != default(DataType)) oparam.OracleType = DataMaper.Map(param.Type);
                            oparam.Direction = DataMaper.Map(param.Direction);
                            oparam.Value = param.Value;
                            command.Parameters.Add(oparam);
                        }
                    result = command.ExecuteNonQuery();
                }
                conn.Close();
            }
            return result;
        }
    }
}